﻿CREATE   PROCEDURE [dbo].[pr_GroupLoad]
@IDG INT,
@DAT DATE
AS
BEGIN
	SELECT 
	NAGR.[IDG] AS 'IDG',
	NAGR.[IDP] AS 'IDP',
	NAGR.[IDD] AS 'IDD',
	PREP.[FAMIO] AS 'Преподаватель',
	PRED.[NAIM] AS 'Дисциплина',
	NAGR.[IDGG] AS 'п/г',
	[CHSEM] AS 'Всего',
	 
	(SELECT COUNT(*)*2 FROM [UROKI] 
	WHERE IDP = NAGR.[IDP] AND IDG = NAGR.[IDG] AND IDD = NAGR.[IDD] AND DAT < @DAT AND IDR = 0) 
	AS 'Вып'

	FROM [dbo].[SPNAGR] AS NAGR
	INNER JOIN
	[dbo].[SPPREP] AS PREP
	ON NAGR.IDP = PREP.IDP
	INNER JOIN
	[dbo].[SPPRED] AS PRED
	ON NAGR.IDD = PRED.IDD
	WHERE NAGR.[IDG] = @IDG
	ORDER BY NAGR.[IDP], NAGR.[IDD];
END



CREATE   PROC [dbo].[pr_TeacherLoad]
@IDP INT,
@DAT DATE

AS
BEGIN
	SELECT 
	NAGR.[IDP] AS 'IDP',
	NAGR.[IDG] AS 'IDG',
	NAGR.[IDD] AS 'IDD',
	GRUP.[NAIM] AS 'Группа',
	PRED.[NAIM] AS 'Дисциплина',
	NAGR.[IDGG] AS 'п/г',
	[CHSEM] AS 'Всего',

	(SELECT COUNT(*)*2 FROM [UROKI] 
	WHERE IDP = NAGR.[IDP] AND IDG = NAGR.[IDG] AND IDD = NAGR.[IDD] AND DAT < @DAT AND IDR = 0) 
	AS 'Вып'

	FROM [dbo].[SPNAGR] AS NAGR
	INNER JOIN
	[dbo].[SPGRUP] AS GRUP
	ON NAGR.IDG = GRUP.IDG
	INNER JOIN
	[dbo].[SPPRED] AS PRED
	ON NAGR.IDD = PRED.IDD
	WHERE NAGR.[IDP] = @IDP
	ORDER BY NAGR.[IDG], NAGR.[IDD];
END



CREATE   PROC [dbo].[pr_LoadNagrTeacher]
@IDP INT
AS
BEGIN
	SELECT 
	NAGR.[IDP] AS 'IDP',
	NAGR.[IDG] AS 'IDG',
	NAGR.[IDD] AS 'IDD',
	GRUP.[NAIM] AS 'Группа',
	PRED.[NAIM] AS 'Дисциплина',
	NAGR.[IDGG] AS 'пг',
	[CHSEM] AS 'Всего' 
	FROM [dbo].[SPNAGR] AS NAGR
	INNER JOIN
	[dbo].[SPGRUP] AS GRUP
	ON NAGR.IDG = GRUP.IDG
	INNER JOIN
	[dbo].[SPPRED] AS PRED
	ON NAGR.IDD = PRED.IDD
	WHERE NAGR.[IDP] = @IDP
	ORDER BY NAGR.[IDG], NAGR.[IDD];
END



CREATE   PROCEDURE [dbo].[CountUroki]
@IDP INT,
@IDG INT,
@IDD INT,
@DAT DATE
AS
BEGIN
	SELECT COUNT(*) FROM [UROKI] WHERE IDP = @IDP AND IDG = @IDG AND IDD = @IDD AND DAT < @DAT AND IDR = 0;
END